﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MediaLibraryManagement.Entity;

namespace MediaLibraryManagement.DAL
{
    public abstract class BaseDAL
    {
        private string _connectionString = string.Empty;
        private SqlConnection _connection;

        protected virtual string TableName { get { return string.Empty; } }

        protected SqlConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = new SqlConnection(_connectionString);
                }
                return _connection;
            }
        }

        public BaseDAL()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["MediaLibraryManagement.Properties.Settings.ConnectionString"].ConnectionString;
            InitializeConnection(_connectionString);
        }

        public BaseDAL(SqlConnection connection)
        {
            _connection = connection;
        }

        #region Public Methods

        public IEntity GetByID(int id)
        {
            IEntity entity = new BaseEntity();
            try
            {
                OpenConnection();
                string commandText = string.Format("SELECT TOP 1 * FROM {0} WHERE ID = {1}", TableName, id);
                using (SqlCommand command = new SqlCommand(commandText, Connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            entity = Fetch(reader);
                        }
                        reader.Close();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return entity;
        }

        public void Delete(int id)
        {
            try
            {
                OpenConnection();
                string commandString = string.Format("DELETE FROM {0} WHERE ID = {1}"
                                                    , TableName
                                                    , id);
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        #endregion

        #region Protected Methods

        protected void ExecuteNonQuery(string commandString, SqlParameter[] parameters)
        {
            try
            {
                OpenConnection();
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    command.Parameters.AddRange(parameters);
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        protected void OpenConnection()
        {
            try
            {
                if (Connection.State == ConnectionState.Closed)
                {
                    Connection.Open();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Không thể mở kết nối tới cơ sở dữ liệu.\n\n" + ex);
            }
        }

        protected void CloseConnection()
        {
            try
            {
                if (Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Không thể đóng kết nối tới cơ sở dữ liệu.\n\n" + ex);
            }
        }

        #endregion

        #region Private Methods

        private void InitializeConnection(string connectionString)
        {
            try
            {
                if (!string.IsNullOrEmpty(_connectionString))
                {
                    _connection = new SqlConnection(connectionString);
                }
                else
                {
                    throw new Exception("ConnectionString is empty.");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected virtual IEntity Fetch(IDataReader reader)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}